Today in my bootcamp we started constructing and running SQL queries in Node.js. Once we covered the basics my instructor showed us how to use "?" as an escape character, which is supposed to somehow prevent SQL injections. I think I get the gist of this concept, but am very confused as to why one would do this instead of putting whatever variable they're inserting inside a template literal. For example:
db.query("DELETE FROM foo WHERE id = ?", [bar], function (err, result){})
vs.
db.query(`DELETE FROM foo WHERE id = ${bar}`, function (err, result){})
My instructor isn't always the best at uh, instructing, but he's proven to be very knowledgeable so I trust this is for a reason. In the past we've noticed that he often uses older techniques that have been largely replaced by more recent additions to the language; is this maybe a case of him doing that?
Direct interpolation like
`DELETE FROM foo WHERE id = ${bar}`
is a bad idea because if bar is dynamically generated, such as taken from the user, this could lead to SQL injection. See Bobby Tables.
While it would be possible in theory to take an input string and make your own attempt to sanitize it properly and then interpolate the result directly yourself - if you mess up, you'll be in trouble. As a result, database drivers often provide a different way of passing in possibly-untrusted input, so that all of that can be abstracted away to the database driver - which makes it a lot easier for developers. That way, instead of each individual user having to implement it (and hope that they do it correctly), the database driver can implement it once and have everyone use that instead.
For this particular case, mysql will analyze the string you pass in and replace ?s with properly escaped versions of the second parameter.
In the past we've noticed that he often uses older techniques that have been largely replaced by more recent additions to the language; is this maybe a case of him doing that?
No, the approach you're showing in your question is very normal and modern.
The first parameter maps to the first question mark. This minimal change ensures that the input values are escaped and, as such, prevents a SQL injection attack here.
check this post why we use ? in queries.
What is the question mark's significance in MySQL at "WHERE column = ?"?